package com.kavitha.db.util;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import com.kavitha.db.dataobject.Employee;
public class EmployeeUtil {
	
	public static List<Employee> getEmployeeDetails(){
		Connection connection = PersistenceManager.getConnection();
		Statement statement;
		List<Employee> list = new ArrayList<Employee>();
		try {
			statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery("select * from employee");
			if(resultSet!=null){
				while(resultSet.next()){
					Employee employee =new Employee();					
					employee.setId(Integer.parseInt(resultSet.getObject(1).toString()));  
					employee.setFirstName((String)resultSet.getObject(2));
					employee.setLastName((String)resultSet.getObject(3));
					employee.setEmailId((String)resultSet.getObject(4));
					employee.setPhoneNo((String)resultSet.getObject(5));
					employee.setDob((Date)resultSet.getObject(6));
					list.add(employee);
				}
				return list;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
		
	}
	
	
	public static void deleteEmpById(String id){
		Connection connection = PersistenceManager.getConnection();
		Statement statement;
		try {
			statement = connection.createStatement();
			statement.executeUpdate("delete from employee where id="+id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static Employee getEmployee(String id){
		Connection connection = PersistenceManager.getConnection();
		Statement statement;
		try {
			statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery("select * from employee where id="+id);
			if(resultSet!=null && resultSet.next()){
				Employee employee =new Employee();					
				employee.setId(Integer.parseInt(resultSet.getObject(1).toString()));  
				employee.setFirstName((String)resultSet.getObject(2));
				employee.setLastName((String)resultSet.getObject(3));
				employee.setEmailId((String)resultSet.getObject(4));
				employee.setPhoneNo((String)resultSet.getObject(5));
				employee.setDob((Date)resultSet.getObject(6));
				return employee;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	
	public static void updateEmployee(HttpServletRequest request){
		String id = request.getParameter("id");
		String firstName = request.getParameter("firstName");
		String lastName = request.getParameter("lastName");
		String emailId = request.getParameter("emailId");
		String phoneNo = request.getParameter("phoneNo");
		String dob = request.getParameter("dob");
		Connection connection = PersistenceManager.getConnection();
		Statement statement;
		try {
			statement = connection.createStatement();
			String query = "update employee set First_Name='"+firstName+"',Last_Name='"+ lastName
					+"', Email_Id='"+emailId+"', Phone_No='"+phoneNo+"', Dob='"+ dob+"' where id="+id;
			statement.executeUpdate(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
}
